OpenEdge Data Management:<br />DataServer for ODBC Modifying tables to support the ROWID function

If you want to use the ROWID function with an ODBC data source, you must make certain changes to your data-source table. The following example uses Sybase to illustrate the necessary changes.

Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null:


alter table `table` add PROGRESS_RECID integer null

Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the numeric data type:


alter table `table` add PROGRESS_RECID_IDENT_ numeric(10,0) identity

Create a trigger to maintain the PROGRESS_RECID column:

create trigger _TI_`table` on table for insert as begin if (select max(inserted.PROGRESS_RECID) from inserted) is NULL begin update `table` set PROGRESS_RECID = @@identify where PROGRESS_RECID is null select convert (int, @@identity) end end

create trigger _TI_table on table for insert as
begin
    if (select max(inserted.PROGRESS_RECID) from inserted) is NULL
    begin
        update table set PROGRESS_RECID = @@identify
            where PROGRESS_RECID is null
        select convert (int, @@identity)
    end
end

Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component:


create index `table##index` on `table` (`column`, PROGRESS_RECID)

If you have already created your schema holder, update it to reflect your changes to the data-source table.

If your data source supports stored procedures, you may modify the steps above to implement the ROWID function using a similar syntax.